package com.example.Dao;

import com.example.JDBCConnection;
import com.example.pojo.Dormitory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * ClassName: DormitoryDAO
 * Package: com.example.Dao
 * Description:
 *
 * @Author: liu
 * @Create: 2025/5/28 15:32
 * @Version:1.0
 */
public class DormitoryDAO {
    // 添加宿舍
    public void addDormitory(Dormitory dorm) {
        String sql = "INSERT INTO dormitory (id, name) VALUES (?, ?)";
        try (Connection conn = JDBCConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, dorm.getName());
            pstmt.setString(2, dorm.getId());
            pstmt.executeUpdate();
            System.out.println("宿舍信息添加成功");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 查看所有宿舍
    public void listDormitories() {
        String sql = "SELECT * FROM dormitory";
        try (Connection conn = JDBCConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            System.out.println("宿舍列表：");
            while (rs.next()) {
                System.out.printf("ID: %s, 名称: %s%n",
                        rs.getString("id"),
                        rs.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    // 修改宿舍信息
    public void updateDormitory(String dormitoryId, String newName) {
        String sql = "UPDATE dormitory SET name = ? WHERE id = ?";
        try (Connection conn = JDBCConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, newName);  // 使用新名称
            pstmt.setString(2, dormitoryId); // 使用宿舍ID
            int rows = pstmt.executeUpdate();
            if (rows > 0) {
                System.out.println("宿舍信息修改成功");
            } else {
                System.out.println("未找到对应的宿舍信息，修改失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    // 查看宿舍人数
    public void getDormitoryOccupancy() {
        String sql = "SELECT d.id, d.name, COUNT(s.id) as occupancy " +
                "FROM dormitory d " +
                "LEFT JOIN student s ON d.id = s.dormitory_id " +
                "GROUP BY d.id, d.name";
        try (Connection conn = JDBCConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            System.out.println("宿舍人数统计：");
            while (rs.next()) {
                System.out.printf("宿舍ID: %s, 宿舍名称: %s, 人数: %d%n",
                        rs.getString("id"),
                        rs.getString("name"),
                        rs.getInt("occupancy"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    // 删除宿舍
    public void deleteDormitory(String dormitoryId) {
        // 检查宿舍是否为空
        String checkSql = "SELECT COUNT(*) FROM student WHERE dormitory_id = ?";
        try (Connection conn = JDBCConnection.getConnection();
             PreparedStatement checkStmt = conn.prepareStatement(checkSql)) {
            checkStmt.setString(1, dormitoryId);
            ResultSet rs = checkStmt.executeQuery();
            if (rs.next() && rs.getInt(1) > 0) {
                System.out.println("宿舍不为空，无法删除");
                return;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return;
        }

        // 执行删除操作
        String deleteSql = "DELETE FROM dormitory WHERE id = ?";
        try (Connection conn = JDBCConnection.getConnection();
             PreparedStatement deleteStmt = conn.prepareStatement(deleteSql)) {
            deleteStmt.setString(1, dormitoryId);
            int rows = deleteStmt.executeUpdate();
            if (rows > 0) {
                System.out.println("宿舍删除成功");
            } else {
                System.out.println("未找到对应的宿舍信息，删除失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 查看单个宿舍的人数
    public void getSingleDormitoryOccupancy(String dormitoryId) {
        String sql = "SELECT d.id, d.name, COUNT(s.id) as occupancy " +
                "FROM dormitory d " +
                "LEFT JOIN student s ON d.id = s.dormitory_id " +
                "WHERE d.id = ? " +
                "GROUP BY d.id, d.name";
        try (Connection conn = JDBCConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, dormitoryId);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                System.out.printf("宿舍ID: %s, 宿舍名称: %s, 人数: %d%n",
                        rs.getString("id"),
                        rs.getString("name"),
                        rs.getInt("occupancy"));
            } else {
                System.out.println("未找到对应的宿舍信息");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


}
